package com.db.yupao.once;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @ProjectName: yupao-back
 * @ClassName: ImportExcelData
 * @Author: LYP
 * @Version: 1.0.0
 * @CreateTime:2025/2/14 12:37
 * @Description: 将excel数据导入数据库
 */

public class ImportExcelData {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/yupao";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456";

    public static void main(String[] args) {
        String fileName = "D:\\Python\\selenium自动化\\output.xlsx";
        // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
        // 具体需要返回多少行可以在`PageReadListener`的构造函数设置
        List<UserInfo> userInfoList = EasyExcel.read(fileName).head(UserInfo.class).sheet().doReadSync();

        Map<String, List<UserInfo>> listMap = userInfoList.stream().collect(Collectors.groupingBy(UserInfo::getUsername));

        // 插入数据到数据库
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 关闭自动提交，使用事务
            conn.setAutoCommit(false);
            Timestamp now = new Timestamp(System.currentTimeMillis());
            // 准备SQL语句
            String sql = "insert into user(id,username,userAccount,avatarUrl,gender,"
                    + "userPassword,tag,phone,email,userStatus,userRole,createTime,updateTime,isDelete) "
                    + "VALUES (?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?)";

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                // 遍历数据并添加到批处理
                for (UserInfo user : userInfoList) {
                    pstmt.setLong(1, user.getId());
                    pstmt.setString(2, user.getUsername());
                    pstmt.setString(3, user.getUserAccount());
                    pstmt.setString(4, user.getAvatarUrl());
                    pstmt.setInt(5, user.getGender());
                    pstmt.setString(6, user.getUserPassword());
                    pstmt.setString(7, user.getTag());
                    pstmt.setString(8, user.getPhone());
                    pstmt.setString(9, user.getEmail());
                    pstmt.setInt(10, user.getUserStatus());
                    pstmt.setInt(11, user.getUserRole());
                    pstmt.setTimestamp(12, now);
                    pstmt.setTimestamp(13, now);
                    pstmt.setInt(14, 0);
                    pstmt.addBatch();
                }

                // 执行批处理
                int[] results = pstmt.executeBatch();
                System.out.println("成功插入 " + results.length + " 条记录");

                // 提交事务
                conn.commit();
            } catch (SQLException e) {
                // 发生错误时回滚
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
}
